﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ServiceModel;
using System.Windows.Forms;


namespace CinemaDbLib
{
    public class Connection
    {
        private SqlConnection connection = null;
        private const string user = "DM75_2;";
        private const string pass = "MaaGodt;";
        private const string dbName = "DM75_2;";
        private const string address = "balder.ucn.dk;";
        private const string conString = "Data Source=" + address +
            "Initial Catalog=" + dbName + "Persist Security Info=True;" +
            "User ID=" + user + "Password=" + pass;

        public Connection()
        {
            connection = new SqlConnection(conString);
        }

        public void Open()
        {
            connection.Open();
        }
        public void Close()
        {
            connection.Close();
        }

        public SqlConnection getConnection()
        {
            return connection;
        }

        // Executes a non query operation and returns the number of affected rows
        public int Execute(string query)
        {
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = query;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
                return -1;
            }
        }

        // Executes a query and returns the sql data reader
        public SqlDataReader Read(string query)
        {
            // Logger.getInstance().LogMessage("DB -> Read: Query( " + query + " )");
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = query;
                return command.ExecuteReader();
            }
            catch (SqlException ex)
            {

                MessageBox.Show(ex.Message);
                //Console.Out.WriteLine("DB Layer Error Encountered: " + ex.Message);
                return null;
            }
        }

        // Execute a query and returns the value of [0, 0] [row, column]
        public Object ExecuteScalar(string query)
        {
            try
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = query;
                return command.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                Console.Out.WriteLine("DB Layer Error Encountered: " + ex.Message);
                return null;
            }
        }

        public int GetMaxId(string table, string column = "id")
        {
            int id;
            string query = "SELECT MAX(" + column + ") FROM " + table;
            try
            {
                this.Open();
                SqlDataReader reader = this.Read(query);
                reader.Read();
                id = reader.GetInt32(0);
                reader.Close();
            }
            catch (Exception)
            {
                id = 1;
            }
            finally
            {
                this.Close();
            }
            return id;
        }

        internal static Connection GetInstance()
        {
            throw new NotImplementedException();
        }
    }
}
